﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;

public partial class Boss_Query : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            fullfillDate();
            showAllOrder();
        }
    }
    private void fullfillDate()
    {
        for (int i = 1995; i < 2018; ++i)
        {
            StartYearDrop.Items.Insert(i - 1995, new ListItem(i.ToString(), i.ToString()));
        }
        StartYearDrop.SelectedIndex = 2017 - 1995;
        for (int i = 1; i < 13; ++i)
        {
            StartMonthDrop.Items.Insert(i - 1, new ListItem(i.ToString(), i.ToString()));
        }
        StartMonthDrop.SelectedIndex = 1 - 1;

        StartYearDrop.SelectedIndex = 2017 - 1995;
        for (int i = 1; i < 32; ++i)
        {
            StartDayDrop.Items.Insert(i - 1, new ListItem(i.ToString(), i.ToString()));
        }
        StartDayDrop.SelectedIndex = 1 - 1;

        for (int i = 1995; i < 2018; ++i)
        {
            EndYearDrop.Items.Insert(i - 1995, new ListItem(i.ToString(), i.ToString()));
        }
        EndYearDrop.SelectedIndex = 2017 - 1995;
        for (int i = 1; i < 13; ++i)
        {
            EndMonthDrop.Items.Insert(i - 1, new ListItem(i.ToString(), i.ToString()));
        }
        EndMonthDrop.SelectedIndex = 1 - 1;

        EndYearDrop.SelectedIndex = 2017 - 1995;
        for (int i = 1; i < 32; ++i)
        {
            EndDayDrop.Items.Insert(i - 1, new ListItem(i.ToString(), i.ToString()));
        }
        EndDayDrop.SelectedIndex = 1 - 1;
        return;
    }
    private void showAllOrder()
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionStrBoss"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            //先通过Aaccount找到所有订单号Dno日期作为约束条件
            //再根据Dno，找到所有信息。
            try
            {
                double sum;
                conn.Open();
                String sqlString = "select sum(Osumprice) as total from Boss_Order where Ostate = '交易完成'";
                //SqlParameter[] parameters = { new SqlParameter("@username", account) };
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    //cmd.Parameters.AddRange(parameters);
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr != null)
                    {
                        dr.Read();
                        string temp = dr["total"].ToString();
                        if (temp != null)
                            sum = Math.Round(double.Parse(temp),2);
                        else
                            sum = 0;
                        SumLab.Text = sum.ToString()+"￥";
                    }
                    dr.Close();
                }

                sqlString = "select sum(Osumprice) as total from Boss_Order where Ostate = '交易完成'";
                //SqlParameter[] parameters = { new SqlParameter("@username", account) };
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    //cmd.Parameters.AddRange(parameters);
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr != null)
                    {
                        dr.Read();
                        string temp = dr["total"].ToString();
                        if (temp != null)
                            sum = Math.Round(double.Parse(temp), 2);
                        else
                            sum = 0;
                        SumLab.Text = sum.ToString() + "￥";
                    }
                    dr.Close();
                }

            }
            catch (Exception ez)
            {
                Response.Write(@"<b>异常信息:</b>" + ez.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
    protected void SearchBut_Click(object sender, EventArgs e)
    {
        string start, end;
        start = StartYearDrop.SelectedValue.ToString() + "-" + StartMonthDrop.SelectedValue.ToString() + "-" + StartDayDrop.SelectedValue.ToString()+" 00:00:00";
        end = EndYearDrop.SelectedValue.ToString() + "-" + EndMonthDrop.SelectedValue.ToString() + "-" + EndDayDrop.SelectedValue.ToString()+ " 23:59:59";
        StartTimeLab.Text = start;
        EndTimeLab.Text = end;       
        showOneOrder( start,end);
    }
    protected void SearchAllBut_Click(object sender, EventArgs e)
    {
        StartTimeLab.Text ="";
        EndTimeLab.Text ="";
        showAllOrder();
    }
    private void showOneOrder(string start  ,string end)
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionStrBoss"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            //先通过Aaccount找到所有订单号Dno日期作为约束条件

            //再根据Dno，找到所有信息。
            try
            {

                double sum;
                conn.Open();
                String sqlString = "select sum(Osumprice) as total from Boss_Order where Ostate = '交易完成' and Ooktime >= @start and Ooktime<= @end ";
                SqlParameter[] parameters = {
                                                new SqlParameter("@start", start),
                                                new SqlParameter("@end", end)
                                            };
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    cmd.Parameters.AddRange(parameters);
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr != null)
                    {
                        dr.Read();
                        string temp = dr["total"].ToString();
                        if (temp != null && temp!="")
                            sum = Math.Round(double.Parse(temp), 2);
                        else
                            sum = 0;
                        SumLab.Text = sum.ToString() + "￥";
                    }
                    dr.Close();
                }

            }
            catch (Exception ez)
            {
                Response.Write(@"<b>异常信息:</b>" + ez.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}